
package com.rt.schedulenew.utils.util;

import java.util.HashMap;
import java.util.Map;

import com.rt.schedulenew.utils.global.ExcelData;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.File;
import java.io.OutputStream;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletResponse;

public class ExcelUtils {
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data, Boolean isMerge) throws Exception {
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
        exportExcel(data, (OutputStream) response.getOutputStream(), isMerge);
    }

    public static int generateExcel(ExcelData excelData, String path) throws Exception {
        File f = new File(path);
        FileOutputStream out = new FileOutputStream(f);
        return exportExcel(excelData, out, false);
    }

    private static int exportExcel(ExcelData data, OutputStream out, Boolean isMerge) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        int rowIndex = 0;
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            HSSFSheet sheet = wb.createSheet(sheetName);
            rowIndex = writeExcel(wb, (Sheet) sheet, data, isMerge);
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            out.close();
        }
        return rowIndex;
    }

    private static int writeExcel(HSSFWorkbook wb, Sheet sheet, ExcelData data, Boolean isMerge) {
        int rowIndex = 0;
        rowIndex = writeTitleBarToExcel(wb, sheet, data);
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles(), rowIndex, data.getFontSize());
        rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex, isMerge, data.getFontSize());
        if (!"".equals(data.getName())) {
            fixedSizeColumns(sheet, data);
        } else {
            autoSizeColumns(sheet, data.getTitles().size() + 1);
        }
        return rowIndex;
    }

    private static void fixedSizeColumns(Sheet sheet, ExcelData data) {
        int[] width = data.getFixedColumns();
        for (int i = 0; i < width.length; ++i) {
            sheet.setColumnWidth(i, 256 * width[i] + 184);
        }
    }

    private static int writeTitleBarToExcel(HSSFWorkbook wb, Sheet sheet, ExcelData data) {
        int rowIndex = 0;
        int colIndex = 0;
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, data.getTitles().size() - 1);
        sheet.addMergedRegion(range);
        Font titleFont = (Font) wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 22);
        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(titleFont);
        Row titleRow = sheet.createRow(rowIndex);
        Cell cell = titleRow.createCell(colIndex);
        cell.setCellValue(StringUtils.isNotBlank((CharSequence) data.getTitle()) ? data.getTitle() : data.getName());
        cell.setCellStyle((CellStyle) titleStyle);
        if (StringUtils.isNotBlank((CharSequence) data.getSelectMemo())) {
            CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, data.getTitles().size() - 1);
            sheet.addMergedRegion(range2);
            ++rowIndex;
            Font titleFont2 = (Font) wb.createFont();
            titleFont2.setFontName("simsun");
            titleFont2.setBold(true);
            titleFont2.setFontHeightInPoints((short) 12);
            HSSFCellStyle titleStyle2 = wb.createCellStyle();
            titleStyle2.setAlignment(HorizontalAlignment.LEFT);
            titleStyle2.setFont(titleFont2);
            Row titleRow2 = sheet.createRow(rowIndex);
            Cell cell2 = titleRow2.createCell(colIndex);
            cell2.setCellValue(data.getSelectMemo());
            cell2.setCellStyle((CellStyle) titleStyle2);
        }
        return ++rowIndex;
    }

    private static int writeTitlesToExcel(HSSFWorkbook wb, Sheet sheet, List<String> titles, int rowIndex, String fontSize) {
        int colIndex = 0;
        Font titleFont = (Font) wb.createFont();
        titleFont.setFontName("微软雅黑");
        int font_size = StringUtils.isNotBlank((CharSequence) fontSize) ? Integer.parseInt(fontSize) : 8;
        titleFont.setFontHeightInPoints((short) font_size);
        titleFont.setColor(IndexedColors.BLACK.index);
        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN);
        Row titleRow = sheet.createRow(rowIndex);
        titleRow.setHeightInPoints(25.0f);
        colIndex = 0;
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle((CellStyle) titleStyle);
            ++colIndex;
        }
        return ++rowIndex;
    }

    private static int writeRowsToExcel(HSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex, Boolean isMerge, String fontSize) {
        Font dataFont = (Font) wb.createFont();
        dataFont.setFontName("微软雅黑");
        int font_size = StringUtils.isNotBlank((CharSequence) fontSize) ? Integer.parseInt(fontSize) : 8;
        dataFont.setFontHeightInPoints((short) font_size);
        dataFont.setColor(IndexedColors.BLACK.index);
        HSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);
        dataStyle.setWrapText(true);
        setBorder(dataStyle, BorderStyle.THIN);
        if (isMerge) {
            Map<Object, Integer> projLines = getLinesbyProId(rows);
            Integer proLineNum = 0;
            Integer lineIndex = 0;
            for (List<Object> rowData : rows) {
                Object proId = rowData.get(0);
                if (lineIndex.equals(proLineNum)) {
                    proLineNum = (projLines.containsKey(proId) ? projLines.get(proId) : Integer.valueOf(1));
                    lineIndex = 0;
                }
                Row dataRow = sheet.createRow(rowIndex);
                int colIndex = 0;
                for (Object cellData : rowData) {
                    Cell cell = dataRow.createCell(colIndex);
                    if (cellData != null) {
                        cell.setCellValue(cellData.toString());
                    } else {
                        cell.setCellValue("");
                    }
                    cell.setCellStyle((CellStyle) dataStyle);
                    ++colIndex;
                }
                if (proLineNum == 1) {
                    ++lineIndex;
                } else if (lineIndex == 0) {
                    CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex + proLineNum - 1, 0, 0);
                    sheet.addMergedRegion(region);
                    ++lineIndex;
                } else {
                    ++lineIndex;
                }
                ++rowIndex;
            }
        } else {
            for (List<Object> rowData2 : rows) {
                Object proId2 = rowData2.get(0);
                Row dataRow2 = sheet.createRow(rowIndex);
                int colIndex = 0;
                for (Object cellData2 : rowData2) {
                    Cell cell2 = dataRow2.createCell(colIndex);
                    if (cellData2 != null) {
                        cell2.setCellValue(cellData2.toString());
                    } else {
                        cell2.setCellValue("");
                    }
                    cell2.setCellStyle((CellStyle) dataStyle);
                    ++colIndex;
                }
                ++rowIndex;
            }
        }
        return rowIndex;
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; ++i) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = sheet.getColumnWidth(i) + 100;
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    private static void setBorder(HSSFCellStyle style, BorderStyle border) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
    }

    private static Map<Object, Integer> getLinesbyProId(List<List<Object>> objs) {
        Map<Object, Integer> lines = new HashMap<Object, Integer>();
        for (List objArray : objs) {
            String proId = objArray.get(0).toString();
            if (lines.containsKey(proId)) {
                Integer num = lines.get(proId);
                lines.put(proId, num + 1);
            } else {
                lines.put(proId, 1);
            }
        }
        return lines;
    }
}
